package org.homly.sql;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;


/**
 * MySql的备份和还原
 * @author 吴浩
 * @version 1.0
 * 2011-6-29
 */
@SuppressWarnings("serial")
public class MySqlBackup implements IBackup{
	
	private DbConfig dbConfig;	
	
	public MySqlBackup(){ }
	public MySqlBackup(DbConfig dbConfig){
		
		this.dbConfig = dbConfig;
	}
	
	/**
	 * 创建mysql备份
	 */
	public void createSqlScript(String filePath) throws Exception{
		File file;
    	File newFile;
    	PrintWriter pwrite;
    	try {
			file =new File(filePath);
			SimpleDateFormat smf = new SimpleDateFormat("yyyy:MM:dd");
			newFile = new File(file.getPath() + "/" + dbConfig.getDbName() + "_" + smf.format(new Date()).replaceAll(" |:", "") + ".sql"); 
			pwrite = new PrintWriter(new OutputStreamWriter(new FileOutputStream(newFile,false), "UTF8"));
			//写入头部信息
			pwrite.println(AppendMessage.headerMessage(dbConfig));
			pwrite.println("SET FOREIGN_KEY_CHECKS=0;" + "\n");
			List<String> tablelists = PublicMethod.getAllTableName(dbConfig, "show tables");
			for (String table : tablelists) {
				StringBuilder strBuilder =new StringBuilder();
				strBuilder.append("show create table `")
				          .append(table).append("`");
				List<String> list = PublicMethod.getAllColumns(dbConfig,strBuilder.toString());
				for (String line : list) {
					//在建表前加说明
					pwrite.println(AppendMessage.tableHeaderMessage(table));
					//生成建表语句
					pwrite.println("DROP TABLE IF EXISTS " + " `" + table + "`;");
					pwrite.println(line + ";" + "\n");
			    }
			}
			pwrite.println(AppendMessage.insertHeaderMessage());
			for (Object table : tablelists) {
				//生成insert语句
				List<Vector<Object>> insertList  =  getAllDatas(dbConfig,"`" + table.toString() + "`");
				for (int i = 0; i < insertList.size(); i++) {
		        	Vector<Object> vector = insertList.get(i);
		        	String tempStr = vector.toString();
		        	tempStr =tempStr.substring(1, tempStr.length()-1);
		        	tempStr ="INSERT INTO " + "`"+ table + "`" + " VALUES(" + tempStr + ");";
		            pwrite.println(tempStr);
				}
			}
			pwrite.flush();
			pwrite.close();
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("出现错误,创建备份文件失败!");
		}
	}

	/**
	 * 还原mysql备份
	 */
	public void executSqlScript(String filePath) throws Exception {
		Connection conn = null;	
		Statement stmt = null;
		List<String> sqlStrList = null;
		try {
			sqlStrList = PublicMethod.loadSqlScript(filePath);
			conn = PublicMethod.getConnection(dbConfig);
			stmt = conn.createStatement();
			// 禁止自动提交
			conn.setAutoCommit(false);
			for (String sqlStr : sqlStrList) {
				int index = sqlStr.indexOf("INSERT");
				if (-1 == index) {
					stmt.addBatch(sqlStr);
				}
			}
			stmt.executeBatch();
			// INSERT语句跟建表语句分开执行，防止未建表先INSERT
			for (String sqlStr : sqlStrList) {
				int index = sqlStr.indexOf("INSERT");
				if (-1 != index) {
					stmt.executeUpdate(sqlStr);
				}																			
			}
			stmt.executeBatch();
			conn.commit();
		} catch (Exception ex) {	
			conn.rollback();
			throw ex;
		}finally{
			DbConnection.closeAll(conn, stmt, null);
		}
	}
	
	/**
	 * 拼接连接字符串
	 * @param dbConfig
	 * @return String
	 * @throws Exception
	 */
	public static String getDbUrl(DbConfig dbConfig) throws Exception {
		StringBuilder urlStr = null;
		try {
			urlStr = new StringBuilder();
			urlStr.append("jdbc:mysql://")
			      .append(dbConfig.getIpNo())
			      .append(":")
			      .append(dbConfig.getPortNo())
			       .append("/")
			      .append(dbConfig.getDbName());
		} catch (Exception e) {
			throw e;
		}
		return urlStr.toString();
	}
	
	/**
	 * 查询生成Insert语句所需的数据
	 * @param tableName
	 * @return List<Vector<Object>>
	 * @throws Exception
	 */
	public List<Vector<Object>> getAllDatas(DbConfig config,String tableName) throws Exception {
		List<Vector<Object>> list ;
		Vector<Object> vector;
		StringBuilder typeStr = null;
		List<TableColumn> columnList;
		StringBuilder sqlStr;
		ResultSet rs= null;
		StringBuilder columnsStr ;
		try {
			//生成查询语句
			typeStr =new StringBuilder();
			sqlStr =new StringBuilder();
			columnsStr =new StringBuilder().append("describe ").append(tableName);
			columnList = PublicMethod.getDescribe(config,columnsStr.toString());
			sqlStr.append("SELECT ");
			for (TableColumn bColumn  : columnList) {
				//处理BLOB类型的数据
				String columnsType = bColumn.getColumnsType();
				if("longblob".equals(columnsType)||"blob".equals(columnsType)||"tinyblob".equals(columnsType)||"mediumblob".equals(columnsType)){
				   typeStr.append("hex(" + "`" + bColumn.getColumnsFiled() + "`" + ") as " + "`" + bColumn.getColumnsFiled() + "`" + " ,");
				}else{
				   typeStr.append("`" + bColumn.getColumnsFiled() + "`" + " ,");
				}
			}
			sqlStr.append(typeStr.substring(0,typeStr.length()-1));
			sqlStr.append(" FROM ").append(""+tableName + ";");
				
			//查询insert语句所需的数据
			list = new ArrayList<Vector<Object>>();	
			rs = PublicMethod.queryResult(config, sqlStr.toString());
			while (rs.next()) {
				vector = new Vector<Object>();
				for (TableColumn dbColumn : columnList) {
					String columnsType = dbColumn.getColumnsType();
					String columnsFile = dbColumn.getColumnsFiled();
					if(null == rs.getString(columnsFile)){
						vector.add( rs.getString(columnsFile));
					//处理BIT类型的数据
					}else if("bit".equals(columnsType.substring(0,3))){
						vector.add(Integer.valueOf(rs.getString(columnsFile)).intValue());
					}else if("bit".equals(columnsType.substring(0,3)) && 0 == Integer.valueOf(rs.getString(columnsFile)).intValue()){
						vector.add("\'"+"\'");
					}else if("longblob".equals(columnsType)||"blob".equals(columnsType)||"tinyblob".equals(columnsType)||"mediumblob".equals(columnsType)){
						vector.add("0x"+rs.getString(columnsFile));
					//处理
					}else if("text".equals(columnsType)||"longtext".equals(columnsType)||"tinytext".equals(columnsType)||"mediumtext".equals(columnsType)){				
						String tempStr =  rs.getString(columnsFile);
						tempStr = tempStr.replace("\'", "\\'");
						tempStr = tempStr.replace("\"", "\\\"");
						vector.add("\'" + tempStr +"\'");
					}else{
						vector.add("\'" + rs.getString(columnsFile) + "\'");
					}
				}
				list.add(vector);
			}
		} catch (Exception e) {
			throw e;
		} finally {				
			DbConnection.closeAll(null, null, rs);
		}
		return list;
	}
}
